|
Boyce–Codd normal form (or BCNF or 3.5NF) is a normal form used in database normalization. It is a slightly stronger version of the third normal form (3NF). BCNF was developed in 1974 by Raymond F. Boyce and Edgar F. Codd to address certain types of anomalies not dealt with by 3NF as originally defined.〔Codd, E. F. "Recent Investigations into Relational Data Base Systems." IBM Research Report RJ1385 (April 23, 1974). Republished in ''Proc. 1974 Congress'' (Stockholm, Sweden, 1974). New York, N.Y.: North-Holland (1974).〕 If a relational schema is in BCNF then all redundancy based on functional dependency has been removed, although other types of redundancy may still exist. A relational schema ''R'' is in Boyce–Codd normal form if and only if for every one of its dependencies ''X → Y'', at least one of the following conditions hold: * ''X → Y'' is a trivial functional dependency (Y ⊆ X) * ''X'' is a super key for schema ''R'' Chris Date has pointed out that a definition of what we now know as BCNF appeared in a paper by Ian Heath in 1971.〔Heath, I. "Unacceptable File Operations in a Relational Database." ''Proc. 1971 ACM SIGFIDET Workshop on Data Description, Access, and Control'', San Diego, Calif. (November 11th–12th, 1971).〕 Date writes: "Since that definition predated Boyce and Codd's own definition by some three years, it seems to me that BCNF ought by rights to be called ''Heath'' normal form. But it isn't."〔Date, C.J. ''Database in Depth: Relational Theory for Practitioners''. O'Reilly (2005), p. 142.〕 Edgar F. Codd released his original paper 'A Relational Model of Data for Large Shared Databanks' in June 1970. This was the first time the notion of a relational database was published. All work after this, including the Boyce-Codd normal form method was based on this relational model. ==3NF table not meeting BCNF (Boyce–Codd normal form)== Only in rare cases does a 3NF table not meet the requirements of BCNF. A 3NF table that does not have multiple overlapping candidate keys is guaranteed to be in BCNF.〔Vincent, M.W. and B. Srinivasan. "A Note on Relation Schemes Which Are in 3NF But Not in BCNF." ''Information Processing Letters'' 48(6), 1993, pp. 281–83.〕 Depending on what its functional dependencies are, a 3NF table with two or more overlapping candidate keys may or may not be in BCNF. An example of a 3NF table that does not meet BCNF is: *Each row in the table represents a court booking at a tennis club that has one hard court (Court 1) and one grass court (Court 2) *A booking is defined by its Court and the period for which the Court is reserved *Additionally, each booking has a Rate Type associated with it. There are four distinct rate types: * *SAVER, for Court 1 bookings made by members * *STANDARD, for Court 1 bookings made by non-members * *PREMIUM-A, for Court 2 bookings made by members * *PREMIUM-B, for Court 2 bookings made by non-members The table's superkeys are: *S1 = *S2 = *S3 = *S4 = *S5 = *S6 = *S7 = *S8 = *ST = , the trivial superkey Note that even though in the above table ''Start Time'' and ''End Time'' attributes have no duplicate values for each of them, we still have to admit that in some other days two different bookings on court 1 and court 2 could ''start at the same time'' or ''end at the same time''. This is the reason why and cannot be considered as the table's superkeys. However, only S1, S2, S3 and S4 are candidate keys (that is, minimal superkeys for that relation) because e.g. S1 ⊂ S5, so S5 cannot be a candidate key. Recall that 2NF prohibits partial functional dependencies of non-prime attributes (i.e. an attribute that does not occur in ANY candidate key) on candidate keys, and that 3NF prohibits transitive functional dependencies of non-prime attributes on candidate keys. In Today's Court Bookings table, there are no non-prime attributes: that is, all attributes belong to some candidate key. Therefore the table adheres to both 2NF and 3NF. The table does not adhere to BCNF. This is because of the dependency Rate Type → Court in which the determining attribute Rate Type on which Court depends is neither a candidate key nor a superset of a candidate key. Dependency Rate Type → Court is respected since a Rate Type should only ever apply to a single Court. The design can be amended so that it meets BCNF: The candidate keys for the Rate Types table are and ; the candidate keys for the Today's Bookings table are and . Both tables are in BCNF. When is a key in the Rate Types table, having one Rate Type associated with two different Courts is impossible, so by using as a key in the Rate Types table, the anomaly affecting the original table has been eliminated. 抄文引用元・出典: フリー百科事典『 ウィキペディア（Wikipedia）』 ■ウィキペディアで「Boyce–Codd normal form」の詳細全文を読む スポンサード リンク
|